package com.opal.stockTaking.mapper.oracle;

import com.opal.system.entity.table.PKOrg;
import com.opal.stockTaking.entity.HSDSummary;
import com.opal.stockTaking.entity.HSDSummaryResultData;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;

@Component
public interface HSDSummaryMapper {
    /**
     * 查询组织
     * @return
     */
    @Select("select name,pk_factory pkOrg from org_factory")
    List<PKOrg> getOrg();

    /**
     * 查询仓库的code和名称
     * @return
     */
    @Select("select code,name  from bd_stordoc where pk_org = #{pkOrg}")
    List<Map<String,String>> getStordoc(String pkOrg);

    /**
     * 调用存储过程需要先清空的表1
     * @return
     */
    @Delete("delete from  cx_stocno")
    int deleteStocno();

    /**
     * 调用存储过程需要先清空的表2
     * @return
     */
    @Delete("delete from  cx_ic_sumary")
    int deleteSumary();

    /**
     * 调用存储过程需要先清空的表3(感觉没必要，一直都是0,可能别的数据会有)
     * @return
     */
    @Delete("delete from  cx_ic_flow")
    int deleteFlow();

    /**
     * 调用存储过程需要先清空的表4(感觉没必要，一直都是0,可能别的数据会有)
     * @return
     */
    @Delete("delete from  cx_ic_flow_b")
    int deleteFlow_b();

    /**
     * 插入
     * @param warehouseCode
     * @return
     */
    @Insert("insert into cx_stocno(stocno) values (#{warehouseCode})")
    int insertStocno(String warehouseCode);

    /**
     * 调用存储过程
     * @param hsdSummary
     */
    @Select("{call ia_gg.gp_cx_sumary(" +
            "p_pk_org => #{pkOrg}," +
            "p_sdate => #{startDate}," +
            "p_edate => #{endDate}," +
            "p_way => #{way}," +
            "p_uuid => #{uid})}")
    void call(HSDSummary hsdSummary);

    /**
     * 查询最终结果
     * @return
     */
    @Select("select * from (select rownum rowno,(select name from org_orgs where pk_org = c.pk_org) 组织,\n" +
            "         stocno 仓库编码,\n" +
            "         STOCNM 仓库,\n" +
            "         itemno 材料编码,\n" +
            "         itemnm 材料名称,\n" +
            "         materialspec 材料规格,\n" +
            "         --(select def12 from bd_material where pk_material = cx_ic_flow.pk_material) 材料类型,\n" +
            "         materialtype 型号,\n" +
            "         materialcategories 材料类型,\n" +
            "         vbatchcode 批次,\n" +
            "         (select code from bd_rack where pk_rack = c.rackno) loc 货位号,\n" +
            "         racknm 货位,\n" +
            "         unitnm 单位,\n" +
            "         UNITWEIGHT 单位克重,\n" +
            "         customsno 归并号,\n" +
            "         customsnm 归并号名称,\n" +
            "         INITQTY 期初数量,\n" +
            "         INIT_MONEY 期初金额,\n" +
            "         IN_QTY 入库数量,\n" +
            "         IN_MONEY 入库金额,\n" +
            "         OUT_QTY 出库数量,\n" +
            "         OUT_MONEY 出库金额,\n" +
            "         END_QTY 结存数量,\n" +
            "         END_MONEY 结存金额,\n" +
            "         PRICE 结存单价 \n" +
            "    from cx_ic_sumary c where rownum <=#{param1}) where rowno>=#{param2}")
    List<Map<String, String>> queryDataMapper(@Param("param1") int param1, @Param("param2") int param2);

    /**
     *
     * @return
     */
    @Select("<script>"+
            "select (select name from org_orgs where pk_org = c.pk_org) pkOrg,\n" +
            "         stocno,\n" +
            "         STOCNM,\n" +
            "         itemno,\n" +
            "         itemnm,\n" +
            "         materialspec,\n" +
            "         materialtype,\n" +
            "         materialcategories,\n" +
            "         vbatchcode,\n" +
            "         (select code from bd_rack where pk_rack = c.rackno) loc,\n" +
            "         racknm,\n" +
            "         unitnm,\n" +
            "         UNITWEIGHT,\n" +
            "         customsno,\n" +
            "         customsnm,\n" +
            "         INITQTY,\n" +
            "         INIT_MONEY initMoney,\n" +
            "         IN_QTY inQty,\n" +
            "         IN_MONEY inMoney,\n" +
            "         OUT_QTY outQty,\n" +
            "         OUT_MONEY outMoney,\n" +
            "         END_QTY endQty,\n" +
            "         END_MONEY endMoney,\n" +
            "         PRICE\n" +
            "    from cx_ic_sumary_new c where uuid =#{uid} and stocno in\n" +
            "<foreach item='item'  collection='stocno' open='(' separator=',' close=')'>" +
            "#{item}" +
            "</foreach>" +"</script>"
            )
    List<HSDSummaryResultData> queryAllDataMapper(@Param("uid") String uid,@Param("stocno") List<String> stocno);

    /**
     * 分页：统计最终结果总数
     * @return
     */

    @Select("select count(1) from cx_ic_sumary")
    int queryDataTotalMapper();
}
